<?php

require('functions/pageLoad.php');

// trades dropdown

	$sql = "SELECT id, trade FROM trades ORDER BY trade";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($rs=mysql_fetch_assoc($query)){

	$tradesDropdown.='<option value="'.$rs['id'].'">'.$rs['trade'].'</option>';

	}

// areas dropdown

	$sql = "SELECT id, area FROM areas ORDER BY area";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($rs=mysql_fetch_assoc($query)){

	$areasDropdown.='<option value="'.$rs['id'].'">'.$rs['area'].'</option>';

	}

if($_SERVER['REQUEST_METHOD'] == 'POST'){

	if($_POST['action'] == 'feedback'){

	if(strlen($_POST['review_id']) > 0){

	$sql = "UPDATE reviews SET rating = '".$_POST['rating']."', review = '".$_POST['review']."' WHERE id = '".$_POST['review_id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Feedback updated</p>';

	}

	else{

	$sql = "INSERT INTO reviews (tradesman_id, rating, review, date_reviewed) VALUES ('".$_GET['id']."', '".$_POST['rating']."', '".$_POST['review']."', NOW())";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Feedback added</p>';

	}

	}

	if($_POST['action'] == 'trade'){

	$sql = "INSERT INTO tradesman_to_trade (tradesman_id, trade_id) VALUES ('".$_GET['id']."', '".$_POST['trade']."')";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Trade added</p>';

	}

	if($_POST['action'] == 'area'){

	$sql = "INSERT INTO tradesman_to_area (tradesman_id, area_id) VALUES ('".$_GET['id']."', '".$_POST['area']."')";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Area added</p>';

	}

	if($_POST['action'] == 'note'){

	$sql = "INSERT INTO notes (tradesman_id, note, note_date) VALUES ('".$_GET['id']."', '".$_POST['note']."', NOW())";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Note added</p>';

	}

}

if(isset($_GET['action'])){

if($_GET['action'] == 'activate' && isset($_GET['id'])){

// activate / deactivate

	$sql = "SELECT user_id, active FROM tradesman WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);

	$new_active = ($rs['active'] == 1) ? '0' : '1';

	$sql = "UPDATE tradesman SET active = '".$new_active."' WHERE id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

if($new_active == '1'){

	$sql = "SELECT firstname, surname, email FROM users WHERE id = '".$rs['user_id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$user_rs=mysql_fetch_assoc($query);

	require_once("classes/class.phpmailer.php");

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->Host = "localhost"; // SMTP server
	$mail->From = "mail@iwantatradesman.co.uk";
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($user_rs['email']);

	$mail->Subject = "Account approved";
	$mail->Body = "Dear ".$user_rs['firstname']." ".$user_rs['surname']."\r\n\r\nCongratulations! Your account has been approved, you now have access to a new stream of work.\r\n\r\nSo login now, update your profile and start quoting for jobs in your area!\r\n\r\nKind regards\r\n\r\nThe I Want A Tradesman Team\r\n\r\n\r\nThis email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.";

	$mail->Send();

}


}

if($_GET['action'] == 'deleteTrade' && isset($_GET['tradeId']) && isset($_GET['id'])){

	$sql = "DELETE FROM tradesman_to_trade WHERE id = '".$_GET['tradeId']."' AND tradesman_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Trade deleted</p>';

}

if($_GET['action'] == 'deleteArea' && isset($_GET['areaId']) && isset($_GET['id'])){

	$sql = "DELETE FROM tradesman_to_area WHERE id = '".$_GET['areaId']."' AND tradesman_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Area deleted</p>';

}

if($_GET['action'] == 'deleteNote' && isset($_GET['noteId']) && isset($_GET['id'])){

	$sql = "DELETE FROM notes WHERE id = '".$_GET['noteId']."' AND tradesman_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Note deleted</p>';

}

if($_GET['action'] == 'deleteReview' && isset($_GET['review_id'])){

	$sql = "DELETE FROM reviews WHERE id = '".$_GET['review_id']."' AND tradesman_id = '".$_GET['id']."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	$results = '<p style="color: #cc0000;">Feedback deleted</p>';

}

}

if(isset($_GET['id'])){

	$sql = "SELECT member_since, company_name, active, username, description, website, trading_name, company_type, reg, vat, established, turnover, insurer, policy, emp_level, pub_level, ref_name_1, ref_tel_1, ref_name_2, ref_tel_2, ref_name_3, ref_tel_3, line_1, line_2, line_3, postcode, area, firstname, surname, telephone, mobile, email, sms_remaining 
		FROM tradesman
		LEFT JOIN users ON tradesman.user_id = users.id 
		LEFT JOIN addresses ON tradesman.address_id = addresses.id 
		LEFT JOIN areas ON addresses.area_id = areas.id 
		LEFT JOIN SMS ON tradesman.id = SMS.tradesman_id 
		WHERE tradesman.id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);

// trades

	$sql = "SELECT tradesman_to_trade.id, trade FROM tradesman_to_trade 
		LEFT JOIN trades ON tradesman_to_trade.trade_id = trades.id 
		WHERE tradesman_id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($trades_rs=mysql_fetch_assoc($query)){

	$trades.=$trades_rs['trade'].', ';
	$currentTrades.=$trades_rs['trade'].' - <a href="?action=deleteTrade&amp;tradeId='.$trades_rs['id'].'&amp;id='.$_GET['id'].'">Delete</a><br />';

	}


// areas

	$sql = "SELECT tradesman_to_area.id, area FROM tradesman_to_area 
		LEFT JOIN areas ON tradesman_to_area.area_id = areas.id 
		WHERE tradesman_id = '".mysql_real_escape_string($_GET['id'])."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($areas_rs=mysql_fetch_assoc($query)){

	$areas.=$areas_rs['area'].', ';
	$currentAreas.=$areas_rs['area'].' - <a href="?action=deleteArea&amp;areaId='.$areas_rs['id'].'&amp;id='.$_GET['id'].'">Delete</a><br />';

	}

// notes

	$sql = "SELECT id, note, note_date FROM notes WHERE tradesman_id = '".mysql_real_escape_string($_GET['id'])."' ORDER BY note_date DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
	while($notes_rs=mysql_fetch_assoc($query)){

	$current_notes.= '<div style="float: left; width: 100%; border-bottom: 2px solid #c3c3c3;"><p><small>'.date("jS M Y", strtotime($notes_rs['note_date'])).'</small></p><p>'.$notes_rs['note'].'</p><p><a href="?action=deleteNote&amp;noteId='.$notes_rs['id'].'&amp;id='.$_GET['id'].'">Delete</a></p></div>';

	}

// feedback

	$sql = "SELECT reviews.id, reviews.job_id, trade, area, title, rating, review FROM reviews 
	LEFT JOIN jobs ON reviews.job_id = jobs.id 
	LEFT JOIN trades ON jobs.trade_id = trades.id 
	LEFT JOIN areas ON jobs.area_id = areas.id 
	WHERE reviews.tradesman_id = '".$_GET['id']."' ORDER BY date_reviewed DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
	while($feedback_rs=mysql_fetch_assoc($query)){

	$job_link = ($feedback_rs['job_id'] != '0') ? '<a href="'.build_job_link($feedback_rs['trade'],$feedback_rs['area'],$feedback_rs['job_id']).'" target="_blank">'.$feedback_rs['title'].'</a>' : 'NA' ;

	$feedback_content.='<div style="float: left; width: 100%; border-bottom: 2px solid #c3c3c3;"><p>
	<strong>Job:</strong> '.$job_link.'<br />
	<strong>Rating:</strong> '.($feedback_rs['rating'] / 2).'<br />
	<strong>Review:</strong> '.$feedback_rs['review'].'<br />
	<a style="color: blue; text-decoration: underline; cursor: pointer;" onclick="return load_feedback(\''.$feedback_rs['id'].'\', \''.$feedback_rs['rating'].'\', \''.str_replace("'","\\'",$feedback_rs['review']).'\');">Edit</a> | <a href="?id='.$_GET['id'].'&amp;action=deleteReview&amp;review_id='.$feedback_rs['id'].'">Delete</a>
	</p></div>';

	}

// jobs completed


	$sql = "SELECT jobs.id, title, trade, area FROM jobs 
		LEFT JOIN trades ON jobs.trade_id = trades.id 
		LEFT JOIN areas ON jobs.area_id = areas.id 
		LEFT JOIN reviews ON jobs.id = reviews.job_id 
		WHERE reviews.tradesman_id = ".$_GET['id'];
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	
	while($jobs_rs=mysql_fetch_assoc($query)){

	$job_link = '<a href="'.build_job_link($jobs_rs['trade'],$jobs_rs['area'],$jobs_rs['_id']).'" target="_blank">'.$jobs_rs['title'].'</a>';

	$jobs_content.=$job_link.'<br />';

	}


// active link

$active_link = ($rs['active'] == 1) ? 'Deactivate' : 'Activate';

	$content = '<h2>Tradesman - '.$rs['company_name'].'</h2>
	
	'.$results.'

	<p><a href="?action=activate&amp;id='.$_GET['id'].'">'.$active_link.' this tradesman</a></p>

	<p><a href="'.build_tradesman_link($rs['username'], $_GET['id']).'" target="_blank">View company profile</a></p>

	<h2>Notes</h2>

	'.$current_notes.'

	<h2>Add note</h2>

	<form method="post" action="tradesman.php?id='.$_GET['id'].'">
	<input type="hidden" name="action" value="note" />
	<div class="formRowWrapper">
	<div class="formInputLeft">Note</div>
	<div class="formInputRight"><textarea name="note" rows="7" cols="60"></textarea></div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">&nbsp;</div>
	<div class="formInputRight"><input type="submit" value="Add note" /></div>
	</div>

	</form>

	<h2>Profile details</h2>

	<div class="formRowWrapper">
	<div class="formInputLeft">ID</div>
	<div class="formInputRight">'.$_GET['id'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Date registered</div>
	<div class="formInputRight">'.date("d/m/Y", strtotime($rs['member_since'])).'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">SMS</div>
	<div class="formInputRight">'.$rs['sms_remaining'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Company name</div>
	<div class="formInputRight">'.$rs['company_name'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Full name</div>
	<div class="formInputRight">'.$rs['firstname'].' '.$rs['surname'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Username</div>
	<div class="formInputRight">'.$rs['username'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Trading name</div>
	<div class="formInputRight">'.$rs['trading_name'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Trades</div>
	<div class="formInputRight">'.$trades.'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Areas</div>
	<div class="formInputRight">'.$areas.'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Address</div>
	<div class="formInputRight">'.$rs['line_1'].', '.$rs['line_2'].', '.$rs['line_3'].', '.$rs['area'].' '.$rs['postcode'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Website</div>
	<div class="formInputRight">'.$rs['website'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Telephone</div>
	<div class="formInputRight">'.$rs['telephone'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Mobile</div>
	<div class="formInputRight">'.$rs['mobile'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Email</div>
	<div class="formInputRight">'.$rs['email'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Description</div>
	<div class="formInputRight">'.$rs['description'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Company type</div>
	<div class="formInputRight">'.$rs['company_type'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Company reg no.</div>
	<div class="formInputRight">'.$rs['reg'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">VAT no.</div>
	<div class="formInputRight">'.$rs['vat'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Established</div>
	<div class="formInputRight">'.date("d/m/Y",strtotime($rs['established'])).'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Annual turnover</div>
	<div class="formInputRight">'.$rs['turnover'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Insurer</div>
	<div class="formInputRight">'.$rs['insurer'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Policy no.</div>
	<div class="formInputRight">'.$rs['policy'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Employee cover</div>
	<div class="formInputRight">'.$rs['emp_level'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Public cover</div>
	<div class="formInputRight">'.$rs['pub_level'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Reference 1</div>
	<div class="formInputRight">'.$rs['ref_name_1'].' - '.$rs['ref_tel_1'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Reference 2</div>
	<div class="formInputRight">'.$rs['ref_name_2'].' - '.$rs['ref_tel_2'].'</div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Reference 3</div>
	<div class="formInputRight">'.$rs['ref_name_3'].' - '.$rs['ref_tel_3'].'</div>
	</div>

	<h2>Completed jobs</h2>

	'.$jobs_content.'

	<h2>Leave Reference/Feedback</h2>

	<form method="post" action="tradesman.php?id='.$_GET['id'].'">
	<input type="hidden" name="review_id" id="review_id" value="" />
	<input type="hidden" name="action" id="action" value="feedback" />
	<div class="formRowWrapper">
	<div class="formInputLeft">Rating</div>
	<div class="formInputRight"><select name="rating" id="rating">
	<option value="0">0</option>
	<option value="1">0.5</option>
	<option value="2">1</option>
	<option value="3">1.5</option>
	<option value="4">2</option>
	<option value="5">2.5</option>
	<option value="6">3</option>
	<option value="7">3.5</option>
	<option value="8">4</option>
	<option value="9">4.5</option>
	<option value="10">5</option>
	</select></div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">Review</div>
	<div class="formInputRight"><textarea name="review" id="review" rows="5" cols="30"></textarea></div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">&nbsp;</div>
	<div class="formInputRight"><input type="submit" id="feedback_submit" value="Leave feedback" /></div>
	</div>

	</form>

	<h2>Current feedback</h2>

	'.$feedback_content.'

	<h2>Edit trades</h2>

	<p>'.$currentTrades.'</p>

	<p><strong>Add trade</strong></p>

	<form method="post" action="tradesman.php?id='.$_GET['id'].'">
	<input type="hidden" name="action" value="trade" />
	<div class="formRowWrapper">
	<div class="formInputLeft">Trade</div>
	<div class="formInputRight"><select name="trade">
	'.$tradesDropdown.'
	</select></div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">&nbsp;</div>
	<div class="formInputRight"><input type="submit" value="Add trade" /></div>
	</div>

	</form>

	<h2>Edit areas</h2>

	<p>'.$currentAreas.'</p>

	<p><strong>Add area</strong></p>

	<form method="post" action="tradesman.php?id='.$_GET['id'].'">
	<input type="hidden" name="action" value="area" />
	<div class="formRowWrapper">
	<div class="formInputLeft">Area</div>
	<div class="formInputRight"><select name="area">
	'.$areasDropdown.'
	</select></div>
	</div>

	<div class="formRowWrapper">
	<div class="formInputLeft">&nbsp;</div>
	<div class="formInputRight"><input type="submit" value="Add area" /></div>
	</div>

	</form>

';


}

else{

	$sql = "SELECT id, company_name, active FROM tradesman ORDER BY id DESC";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rows = mysql_num_rows($query);

	if($rows > 0){
	$limit = ($_GET['page'] < 2) ? '0,20' : (($_GET['page']-1)*20).',20';
	}

	$sql.=' LIMIT '.$limit;
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

	if($rows > 0){

	$content = '<h2>Tradesman</h2><table width="100%"><tr><th style="text-align: left">Company</th><th>Active</th></tr>';

	while($rs=mysql_fetch_assoc($query)){

	$active = ($rs['active'] == 1) ? 'tick' : 'cross';

	$content.= '<tr><td style="text-align: left;"><a href="?id='.$rs['id'].'">'.$rs['company_name'].'</a></td><td><img src="images/'.$active.'.png" alt="" /></td></tr>';

	}

	$content.= '<tr><td colspan="2" align="right">'.pageSelector($rows, $_GET['page'], 20).'</td></tr></table>';

	}

	else{

	$content= '<h2>Tradesman</h2><p>There are currently no tradesmen in the database.</p>';

	}

}

require('includes/meta.php');
require('includes/header.php');
require('includes/leftColumn.php');
?><div id="middleColumn"><div id="content">
<?php echo $content; ?>
</div>
</div>
</div>

<?php
require('includes/footer.php');
?>